package edu.washington.vaccine.backend;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import edu.washington.vaccine.*;

/*
 * 
 * Establishes local db for the vaccine and patient data
 */
public class VaccineLocalHelper extends android.database.sqlite.SQLiteOpenHelper {
	private static final String DATABASE_NAME = "constants.db";
//	static final String TABLE_NAME="Vaccines";
//	static final String COL1="VaccineID";
//	static final String COL2="VaccineName";
//	static final String COL3="FollowUpVaccine";
//	static final String COL4="WeeksUntilNextVaccine";
//	static final String COL5="Schedule";
	private static VaccineLocalHelper instance = null;
	private Calendar c = Calendar.getInstance();
	private Context context;
	
	public static VaccineLocalHelper getInstance(Context context) {
		if (instance == null) {
			instance = new VaccineLocalHelper(context);
		}
		return instance;
	}
	
	private VaccineLocalHelper(Context context) {
		super(context, DATABASE_NAME, null, 1);
		this.context = context;
	}

	//creates sqlite local db for Vaccines.
	//information is sourced from raw/vaccines.txt.
	//all Vaccine params are represented in the table
	@Override
	public void onCreate(SQLiteDatabase db) {
		
		Scanner s = new Scanner(readRawTextFile(context, R.raw.vaccines));
		Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='constants'", null);
		try {
			if (c.getCount() == 0) {
				db.execSQL("CREATE TABLE Vaccines (vid integer primary key, vname text, vdesc text, vschedule text)");

				ContentValues cv = new ContentValues();
				int vaccineNumber = 1;
				
				while(s.hasNextLine()) {
					String currentVaccine = s.nextLine();
					String[] vaccineData = currentVaccine.split(", ");
					cv.put("vid", vaccineNumber);
			        cv.put("vname", vaccineData[0]);
			        cv.put("vdesc", vaccineData[1]);
			        cv.put("vschedule", vaccineData[2]);
			        db.insert("Vaccines", null, cv);
			        vaccineNumber++;
				}
			}
		}
		finally {
			c.close();
		}
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		android.util.Log.w("Constants", "Upgrading database, which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS constants");
		onCreate(db);
	}

	//helper method for reading the raw txt file of vaccines and
	//returns a single string with each complete vaccine entry
	//separated by a new-line char
	private static String readRawTextFile(Context ctx, int resId) {
		InputStream inputStream = ctx.getResources().openRawResource(resId);

		InputStreamReader inputreader = new InputStreamReader(inputStream);
		BufferedReader buffreader = new BufferedReader(inputreader);
		String line;
		StringBuilder text = new StringBuilder();
		
		try {
			while (( line = buffreader.readLine()) != null) {
				text.append(line + "\n");
			}
		} catch (IOException e) {
			return null;
		}
		return text.toString();
	}
	
	//edit Patient p data on local db
	public void editPatient(Patient p) {
		Cursor c = getReadableDatabase().rawQuery("select editdate from Patients where pid = \"" + p.id + "\"", null);
		c.moveToNext();
		
		ContentValues values = new ContentValues();
		values.put("pid", p.id);
		values.put("fname", p.fname);
		values.put("lname", p.lname);
		values.put("dob", p.dob);
		values.put("location", p.location);
		values.put("fathername", p.father);
		values.put("mothername", p.mother);
		values.put("editdate", c.getString(0));
		values.put("notes", p.notes);
		getWritableDatabase().update("Patients", values, "pid = \"" + p.id + "\"", null);
		getWritableDatabase().insert("editedPatients", null, values);
	}
	
	//adds a new Patient to the local db
	public boolean addPatient(Patient p) {
//		String pid = "" + p.id;
//		String lname = p.lname;
//		String fname = p.fname;
//		String dob = p.dob;
//		String location = p.dob;
//		String fathername = p.dob;
//		String mothername = p.dob;
//		String notes = p.dob;
		
    	ContentValues values = new ContentValues();
    	values.put("pid", p.id);
    	values.put("lname", p.lname);
    	values.put("fname", p.fname);
    	values.put("dob", p.dob);
    	values.put("location", p.location);
    	values.put("fathername", p.father);
    	values.put("mothername", p.mother);
    	values.put("notes", p.notes);
    	getWritableDatabase().insert("Patients", null, values);
    	getWritableDatabase().insert("newPatients", null, values);
    	
//    	VaccineDB.getInstance().addToPatientContainer(p);
    	return true;
	}
	
	//adds a vaccine and its associated date to the given patient
	//id in the patienthistory table
	//the date placed into the table is the current date
	public boolean addVaccine(String pid, String vid) {
		Date d = c.getTime();
		ContentValues values = new ContentValues();
		values.put("pid", pid);
		values.put("vid", vid);
//		String date = d.getMonth() + "-" + d.getDay() + "-" + d.getYear();
		String date = "";
		int month = d.getMonth() + 1;
		int day = d.getDate();
		int year = d.getYear() + 1900;

		if (month < 10)
			date = "0";
		date += month + "-";

		if (day < 10)
			date += "0";
		date += day + "-" + year;
		values.put("dategiven", date);
		
		getWritableDatabase().insert("PatientHistory", null, values);
		getWritableDatabase().insert("newPatientHistory", null, values);
		
//		VaccineDB.getInstance().addToPatientHistoryContainer(new String[]{pid, vid, date});
		return true;
	}
	
	// Requires proper date format: "month-day-year";
	// adds pid, vid, and a client specified date
	public boolean addVaccineWithDate(String pid, String vid, String date) {
//		Date d = c.getTime();
		ContentValues values = new ContentValues();
		values.put("pid", pid);
		values.put("vid", vid);
		values.put("dategiven", date);
		getWritableDatabase().insert("PatientHistory", null, values);
		getWritableDatabase().insert("newPatientHistory", null, values);
		
//		VaccineDB.getInstance().addToPatientHistoryContainer(new String[]{pid, vid, date});
		return true;
	}
	
	//returns an iterator of Patients.
	public Iterator<Patient> getPatientsFromDB() {
		List<Patient> patientList = new ArrayList<Patient>();
		Cursor cursor = getWritableDatabase().rawQuery("select * from Patients order by lname", new String[]{});
        // output
        while (cursor.moveToNext()) {
        	int pid = cursor.getInt(0);
        	String lname = cursor.getString(1);
        	String fname = cursor.getString(2);
        	String dob = cursor.getString(3);
        	String location = cursor.getString(4);
        	String fathername = cursor.getString(5);
        	String mothername = cursor.getString(6);
        	String editdate = cursor.getString(7);
        	String conflict = cursor.getString(8);
        	String notes = cursor.getString(9);
        	
			Patient p = new Patient(pid, lname, fname, dob, location, fathername, mothername, editdate, conflict, notes);
			patientList.add(p);
        }
        cursor.close();
		return patientList.iterator();
	}
	
	//String pid returns all the vaccines and dates of the given patient
	//format: vname, dategiven\n
	public String getPatientsVaccines(String pid) {
		StringBuilder sb = new StringBuilder();
		Cursor cursor = getReadableDatabase().rawQuery("SELECT vname, dategiven FROM PatientHistory ph, Vaccines v " +
    			"WHERE ph.vid = v.vid AND pid = " + "\"" + pid + "\"", new String[]{});
        // output
        while (cursor.moveToNext()) {
        	String vname = cursor.getString(0);
        	String dategiven = cursor.getString(1);
			sb.append(vname + ", " + dategiven + "\n");
        }
        cursor.close();
		return sb.toString();
	}
	
	//returns a set of String[] with each String[] 
	//representing a vaccine given to the patient.
	//Each String[] contains two indexes
	//[0] = vaccine.id and [1] = vaccine.vname.
	public Set<String[]> getPatientsVaccinesWithId(String pid) {
		Cursor cursor = getReadableDatabase().rawQuery("SELECT vid, dategiven FROM PatientHistory where pid = " + "\"" + pid + "\"", new String[]{});
        // output
		Set<String[]> vaccines = new HashSet<String[]>();
        while (cursor.moveToNext()) {
        	String[] info = new String[2];
			info[0] = "" + cursor.getInt(0);
			info[1] = cursor.getString(1);
			vaccines.add(info);
        }
        cursor.close();
        return vaccines;
	}
	
	//Returns an iterator of Patients that contain any one field that
	// match at least one of the given fields in parameter p
	public Iterator<Patient> searchPatientsFromDB(Patient p) {
		int pid = p.id;
		String fname = p.fname;
		String lname = p.lname;
//		String dob = p.dob;
		String location = p.location;
		String fathername = p.father;
		String mothername = p.mother;
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from Patients");
		if (pid != 0 || !lname.equals("") || !fname.equals("")) { //|| dob != "") {
			sb.append(" where");
		}
	    boolean first = false;
	    if (pid != 0) {
	    	first = true;
	    	sb.append(" pid = " + "\"" + pid + "\"");
	    }
	    if (!lname.equals("")) {
	    	if (first) {
	    		sb.append(" and");
	    	}
	    	first = true;
	    	sb.append(" lname = " + "\"" + lname + "\"");
	    }
	    if (!fname.equals("")) {
	    	if (first) {
	    		sb.append(" and");
	    	}
	    	first = true;
	    	sb.append(" fname = " + "\"" + fname + "\"");
	    }
//	    if (!dob.equals("")) {
//	    	if (first) {
//	    		sb.append(" and");
//	    	}
//	    	sb.append(" dob = " + dob);
//	    }
	    if (!location.equals("")) {
	    	if (first) {
	    		sb.append(" and");
	    	}
	    	first = true;
	    	sb.append(" location = " + "\"" + location + "\"");	    	
	    }
	    if (!fathername.equals("")) {
	    	if (first) {
	    		sb.append(" and");
	    	}
	    	first = true;
	    	sb.append(" fathername = " + "\"" + fathername + "\"");
	    }
	    if (!mothername.equals("")) {
	    	if (first) {
	    		sb.append(" and");
	    	}
	    	first = true;
	    	sb.append(" mothername = " + "\"" + mothername + "\"");
	    }
	    
	    
	    if (sb.toString().equals("select * from Patients")) {
	    	sb.append(" where pid != 0 order by lname");
	    } else {
	    	sb.append(" order by lname");
	    }
	    
	    SQLiteDatabase db = getReadableDatabase();
	    
    	Cursor cursor = db.rawQuery(sb.toString(), new String[]{});
        // output
    	List<Patient> patient = new ArrayList<Patient>();
    	
        while (cursor.moveToNext()) {
        	int pid2 = cursor.getInt(0);
        	String lname2 = cursor.getString(1);
        	String fname2 = cursor.getString(2);
        	String dob2 = cursor.getString(3);
        	String location2 = cursor.getString(4);
        	String fathername2 = cursor.getString(5);
        	String mothername2 = cursor.getString(6);
        	String editdate2 = cursor.getString(7);
        	String conflict2 = cursor.getString(8);
        	String notes2 = cursor.getString(9);
        	
			Patient p2 = new Patient(pid2, lname2, fname2, dob2, location2, fathername2, mothername2, editdate2, conflict2, notes2);
			patient.add(p2);
        }
        cursor.close();
        return patient.iterator();
	}
	
	//Updates the online database with the local cache of Patients
	//Passes all entries in the local database
	public void update() {
		VaccineDB db = VaccineDB.getInstance();
		
		Cursor cursor = getReadableDatabase().rawQuery("select * from newPatients", new String[]{});
        while (cursor.moveToNext()) {
        	int pid = cursor.getInt(0);
        	String lname = cursor.getString(1);
        	String fname = cursor.getString(2);
        	String dob = cursor.getString(3);
        	String location = cursor.getString(4);
        	String fathername = cursor.getString(5);
        	String mothername = cursor.getString(6);
        	String editdate = cursor.getString(7);
        	String conflict = cursor.getString(8);
        	String notes = cursor.getString(9);
        	
			Patient p = new Patient(pid, lname, fname, dob, location, fathername, mothername, editdate, conflict, notes);
        	db.addPatient(p);
        }
        cursor.close();
        
        cursor = getReadableDatabase().rawQuery("select * from newPatientHistory", new String[]{});
        while (cursor.moveToNext()) {
        	String pid = cursor.getString(0);
        	String vid = cursor.getString(1);
        	String dategiven = cursor.getString(2);
        	db.addVaccineFromOffline(pid, vid, dategiven);
        }
        cursor.close();

        cursor = getReadableDatabase().rawQuery("select * from editedPatients", new String[]{});
        while (cursor.moveToNext()) {
        	int pid = cursor.getInt(0);
        	String lname = cursor.getString(1);
        	String fname = cursor.getString(2);
        	String dob = cursor.getString(3);
        	String location = cursor.getString(4);
        	String fathername = cursor.getString(5);
        	String mothername = cursor.getString(6);
        	String editdate = cursor.getString(7);
        	String conflict = cursor.getString(8);
        	String notes = cursor.getString(9);
        	
			Patient p = new Patient(pid, lname, fname, dob, location, fathername, mothername, editdate, conflict, notes);
        	db.editPatient(p);
        }
        cursor.close();
        
        SQLiteDatabase sdb = getWritableDatabase();
        sdb.delete("newPatients", null, null);
        sdb.delete("newPatientHistory", null, null);
        sdb.delete("editedPatients", null, null);

	}
	
}
